' @mas76nrm 2023/11/26Private Function WorksheetExists(shtName As String, Optional wb As Workbook) As BooleanDim sht As Worksheet If wb Is Nothing Then Set wb = ThisWorkbookOn Error Resume NextSet sht = wb.Sheets(shtName)On Error GoTo 0WorksheetExists = Not sht Is NothingEnd Function'Function WorksheetExists(sName As String) As Boolean' WorksheetExists = Evaluate("ISREF('" & sName & "'!A1)")'End Function'Private Function getCity(cityPersianName As String)' Dim cities As Variant' cities = Array( _' Array("مرکز استان", "Center"), _' Array("اردب?ل", "Ardabil"), _' Array("پارس آباد", "Parsabad"), _' Array("بيله سوار", "BileSavar") _' )' Dim i As Integer' getCity = "test"' For i = 0 To 3' If cities(i)(0) = cityPersianName Then' getCity = cities(i)(1)' Exit For' End If' Next i'End FunctionPrivate Function copyRow(sourceSheet As String, destinationSheet As String, rowNumber As Integer, Optional increaseRow As Boolean = False)Dim ws1 As Worksheet, ws2 As WorksheetDim lastRow As Long Set ws1 = ThisWorkbook.Sheets(sourceSheet)Set ws2 = ThisWorkbook.Sheets(destinationSheet)lastRow = ws2.Cells(Rows.Count, "A").End(xlUp).RowIf increaseRow ThenlastRow = lastRow + 1End Ifws1.Rows(rowNumber).EntireRow.copy ws2.Range("A" & lastRow)copyRow = TrueEnd FunctionPublic Sub extractCities() Dim rowCounter As IntegerDim city As StringDim res As BooleanThisWorkbook.Sheets("Personels").SelectFor

How to Split an Excel Sheet into Multiple WorksheetsExcelZakhar Yung11min readJan 24, 2024Table of ContentshideHow to split an Excel sheet into multiple worksheets: VBA optionVBA script to split Excel sheet into multiple sheets based on column valueVBA script to split Excel sheet into multiple files based on column valueHow to split Excel sheet into multiple worksheets based on rowsHow to split Excel sheets into multiple worksheets without VBASplit an Excel sheet manuallyHow to automate data import to ExcelWhat’s wrong with using VBA for splitting Excel sheets into multiple sheets?When we talk about splitting data in a spreadsheet, it means data partitioning – dividing a dataset into smaller subsets. These are extracted based on a shared attribute of a column (vertical split) or row (horizontal split). Excel, despite its supremacy in functionality, doesn’t offer any functions for splitting data. So, to split an Excel sheet into multiple worksheets, you’ll either need to do this manually or with VBA Macro Editor. In this tutorial, I’m going to reveal the ins and outs of each way, so buckle up and let’s go! How to split an Excel sheet into multiple worksheets: VBA optionThe main value of splitting datasets is to obtain smaller chunks of data that are easier to manage and analyze. You may not need to split an Excel sheet into multiple worksheets if the row count is up to 50. However, if you deal with a tremendously large dataset that is frequently refreshed, then it makes sense